package kq.lzj;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ConvertData {

	public static void main(String[] args) {
		List<Examinee> examinees = new ArrayList<Examinee>();
		
		List<ResultData> results = new ArrayList<ResultData>();
	
        try {
            FileInputStream fileInputStream = new FileInputStream(new File("D:\\1.xls"));
            Workbook workbook = new XSSFWorkbook(fileInputStream);

            // 选择要读取的工作表（通常是第一个工作表，索引为0）
            Sheet sheet = workbook.getSheetAt(0);

            int numRows = sheet.getPhysicalNumberOfRows();
            for (int rowIndex = 1; rowIndex < numRows; rowIndex++) {
                Row row = sheet.getRow(rowIndex);

                if (row != null) {
                	Examinee examinee = new Examinee();
                	
                    examinee.setSfzh(row.getCell(7).getStringCellValue());
                    examinee.setBxkm(row.getCell(10).getStringCellValue());
                    examinee.setXkkm(row.getCell(11).getStringCellValue());    
                     
                    examinees.add(examinee);
                }
            }

            fileInputStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        
        for(Examinee examinee : examinees) 
        {
        	if(null != examinee.getBxkm() && examinee.getBxkm().trim().length() > 0 
        			&& !examinee.getBxkm().equals("无必考")) 
        	{
        		ResultData result = new ResultData();
        		result.setSfzh(examinee.getSfzh());
        		result.setIsBx("1");
        		result.setKm(examinee.getBxkm().trim());
        		results.add(result);
        	}
        	
        	if(null != examinee.getXkkm() && examinee.getXkkm().trim().length() > 0 && !examinee.getBxkm().equals("无选考"))
        	{
        		String[] xkkms = examinee.getXkkm().split(",");
        		for(String xkkm : xkkms) 
        		{
        			if(null != xkkm && !xkkm.equals("无选考"))
        			{
        				ResultData result = new ResultData();
                		result.setSfzh(examinee.getSfzh());
                		result.setIsBx("0");
                		result.setKm(xkkm);
                		results.add(result);
     				}
        		}
        	}
        	
        }
        
        
        try {
            // 指定输出的文件名
            String outputFileName = "D://2.xlsx";

            // 如果文件已存在，先删除它
            File outputFile = new File(outputFileName);
            if (outputFile.exists()) {
                if (outputFile.delete()) {
                    System.out.println("已删除现有文件: " + outputFileName);
                } else {
                    System.err.println("无法删除现有文件: " + outputFileName);
                    return;
                }
            }

            try (Workbook workbook = new XSSFWorkbook()) {
                Sheet sheet = workbook.createSheet("Sheet1");

                // 创建标题行
                Row headerRow = sheet.createRow(0);
                headerRow.createCell(0).setCellValue("sfzh");
                headerRow.createCell(1).setCellValue("km");
                headerRow.createCell(2).setCellValue("isBx");

                // 写入数据
                for (int rowIndex = 0; rowIndex < results.size(); rowIndex++) {
                    Row dataRow = sheet.createRow(rowIndex + 1);
                    ResultData resultData = results.get(rowIndex);

                    dataRow.createCell(0).setCellValue(resultData.getSfzh());
                    dataRow.createCell(1).setCellValue(resultData.getKm());
                    dataRow.createCell(2).setCellValue(resultData.getIsBx());
                }

                // 保存Excel文件
                try (FileOutputStream fileOut = new FileOutputStream(outputFileName)) {
                    workbook.write(fileOut);
                }

                System.out.println("Excel 文件已生成成功！");
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
		
	}
}
